﻿Imports System.Configuration
Imports System.Data.SqlClient

Public Class frmUpdateCostQtyRSP

    Dim util As New AppsUtility.EncDec
    Public strCon As String = util.toDecrypt(ConfigurationManager.AppSettings("SQLString"))


    Private Sub frmUpdateCostQtyRSP_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call LoadCompany()
        Call toLoadProducts()
        Me.Location = New Point(5, 95)
    End Sub

    Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
        Dim strsql As String
        Dim ProdId As Integer

        Dim OnHand As Integer
        Dim Price1 As Decimal
        Dim Price2 As Decimal
        Dim Price3 As Decimal
        Dim AvgCost As Decimal

        Dim QtyTotal As Integer

        If txtOnHand.Text.Trim = "" Then
            OnHand = 0
        Else
            OnHand = txtOnHand.Text.Trim
        End If

        If txtPrice1.Text.Trim = "" Then
            Price1 = 0
        Else
            Price1 = txtPrice1.Text.Trim
        End If

        If txtPrice2.Text.Trim = "" Then
            Price2 = 0
        Else
            Price2 = txtPrice2.Text.Trim
        End If

        If txtPrice3.Text.Trim = "" Then
            Price3 = 0
        Else
            Price3 = txtPrice3.Text.Trim
        End If

        If txtAvgCost.Text.Trim = "" Then
            AvgCost = 0
        Else
            AvgCost = txtAvgCost.Text.Trim
        End If

        strsql = "select ProductId from  Product p with (NoLock) where p.ProductCode like '" & txtDisplay.Text.Trim & "' "
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim cmd As New SqlCommand(strsql, sqlCon)
        cmd.CommandType = CommandType.Text
        sqlCon.Open()
        ProdId = cmd.ExecuteScalar

        If ProdId > 0 Then
            strsql = "update ShowroomRol set PresentQty=" & OnHand & " , CommittedQty=" & OnHand & "  Where ShowroomId=" & cmbDivStore.SelectedValue & " and ProductId=" & ProdId
            Dim cmd1 As New SqlCommand(strsql, sqlCon)
            cmd1.CommandType = CommandType.Text
            cmd1.ExecuteNonQuery()

            strsql = " Select sum(PresentQty) from ShowroomRol  Where ShowroomId=" & cmbDivStore.SelectedValue & " and ProductId=" & ProdId
            Dim cmd2 As New SqlCommand(strsql, sqlCon)
            cmd2.CommandType = CommandType.Text
            QtyTotal = cmd2.ExecuteScalar

            strsql = "update Product set AverageCost=" & AvgCost & " , QtyOnHand=" & QtyTotal & ",FirstPrice=" & Price1 & ",SecondPrice=" & Price2 & ",ThirdPrice=" & Price3 & " Where ProductId=" & ProdId
            Dim cmd3 As New SqlCommand(strsql, sqlCon)
            cmd3.CommandType = CommandType.Text
            cmd3.ExecuteNonQuery()

            sqlCon.Close()
            MsgBox("Successfully Updated", 64, "Inventory Manager")
            txtAvgCost.Clear()
            txtDisplay.Clear()
            txtOnHand.Clear()
            txtPrice1.Clear()
            txtPrice2.Clear()
            txtPrice3.Clear()
            txtDisplay.Focus()

        Else
            MsgBox("Check Product Display Criteria", 16, "Inventory Manager")
            txtDisplay.Focus()
        End If
    End Sub

    Private Sub cmdExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExit.Click
        Me.Close()
    End Sub

    Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click
        Call toLoadProducts()
    End Sub

    Private Sub cmbCompany_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbCompany.SelectedIndexChanged
        If IsNumeric(cmbCompany.SelectedValue) Then
            Call LoadDivisions()
        End If
    End Sub

    Sub LoadCompany()
        Dim APdata As New APdataLogic
        Dim ds As DataSet
        ds = APdata.GetAllCompany()
        cmbCompany.DataSource = ds.Tables(0)
        cmbCompany.ValueMember = "CompanyID"
        cmbCompany.DisplayMember = "CompanyName"
    End Sub

    Sub LoadDivisions()
        Dim APdata As New APdataLogic
        Dim ds As DataSet
        ds = APdata.GetAllDivisions(cmbCompany.SelectedValue)
        cmbDivStore.DataSource = ds.Tables(0)
        cmbDivStore.ValueMember = "DepartmentID"
        cmbDivStore.DisplayMember = "DepartmentName"
    End Sub

    Sub toLoadProducts()
        Me.Cursor = Cursors.WaitCursor
        Dim strsql As String
        strsql = "select * from  Product p with (NoLock) where p.ProductCode like '" & txtSearch.Text.Trim & "%' "
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        Dim da As New SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")

        Try
            da.SelectCommand.CommandTimeout = 1800
            da.Fill(ds, "PRODUCT")
            dgProduct.DataSource = ds.Tables("PRODUCT")
            Call toInitDataGrid()

        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Me.Cursor = Cursors.Default
    End Sub
    Sub toInitDataGrid()
        With dgProduct
            .TableStyles.Clear()
            .CaptionBackColor = System.Drawing.Color.SlateBlue
            .CaptionForeColor = System.Drawing.Color.LemonChiffon
            .AllowSorting = True
            .AlternatingBackColor = System.Drawing.Color.AliceBlue
            .ParentRowsBackColor = System.Drawing.Color.Lavender
            .ParentRowsForeColor = System.Drawing.Color.SlateBlue
            .HeaderBackColor = System.Drawing.Color.LemonChiffon
            .AllowNavigation = False
            .ParentRowsVisible = False
            .RowHeadersVisible = False
        End With
        Dim cm As CurrencyManager = BindingContext(dgProduct.DataSource, dgProduct.DataMember)
        Dim dv As DataView = cm.List
        dv.AllowNew = False

        Dim grdTableStyle As New DataGridTableStyle
        With grdTableStyle
            .AlternatingBackColor = Color.AliceBlue
            .HeaderFont = New Font("calibri", 9.0!, FontStyle.Bold)
            .MappingName = "PRODUCT" '
            .PreferredColumnWidth = 160
            .HeaderBackColor = Color.LightSlateGray
            .HeaderForeColor = Color.LightCyan
            .PreferredRowHeight = 15
            .RowHeadersVisible = False
            .RowHeaderWidth = 0
        End With
        Dim cs1 As DataGridColumnStyle
        '
        cs1 = New DataGridTextBoxColumn
        cs1.Width = 0
        cs1.MappingName = "PRODUCTID"
        cs1.HeaderText = "CODE"
        cs1.NullText = ""
        cs1.ReadOnly = True
        grdTableStyle.GridColumnStyles.Add(cs1)
        '
        cs1 = New DataGridTextBoxColumn
        cs1.Width = 100
        cs1.MappingName = "PRODUCTCODE"
        cs1.HeaderText = "CODE"
        cs1.NullText = ""
        cs1.ReadOnly = True
        grdTableStyle.GridColumnStyles.Add(cs1)
        '
        cs1 = New DataGridTextBoxColumn
        cs1.Width = 100
        cs1.MappingName = "MANUFPRODUCTCODE"
        cs1.HeaderText = "MANUF.CODE"
        cs1.NullText = ""
        cs1.ReadOnly = True
        grdTableStyle.GridColumnStyles.Add(cs1)
        '
        cs1 = New DataGridTextBoxColumn
        cs1.Width = 200
        cs1.MappingName = "SHORTDESCRIPTIONE"
        cs1.HeaderText = "DESCRIPTION"
        cs1.NullText = ""
        cs1.ReadOnly = True
        grdTableStyle.GridColumnStyles.Add(cs1)

        cs1 = New DataGridTextBoxColumn
        cs1.Width = 70
        cs1.MappingName = "AverageCost"
        cs1.HeaderText = "AVG.COST"
        cs1.NullText = ""
        cs1.ReadOnly = True
        cs1.Alignment = HorizontalAlignment.Right
        grdTableStyle.GridColumnStyles.Add(cs1)

        dgProduct.TableStyles.Add(grdTableStyle)

    End Sub

    Private Sub cmdDisplay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDisplay.Click
        Dim strsql As String
        strsql = "select * from  Product p with (NoLock) where p.ProductCode = '" & txtDisplay.Text.Trim & "'"
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        Dim da As New SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet()
        Try
            da.SelectCommand.CommandTimeout = 1800
            da.Fill(ds)
            If ds.Tables(0).Rows.Count > 0 Then
                txtPrice1.Text = ds.Tables(0).Rows(0).Item("FIRSTPRICE").ToString()
                txtPrice2.Text = ds.Tables(0).Rows(0).Item("SECONDPRICE").ToString()
                txtPrice3.Text = ds.Tables(0).Rows(0).Item("THIRDPRICE").ToString()
                txtAvgCost.Text = ds.Tables(0).Rows(0).Item("AVERAGECOST").ToString()
                txtOnHand.Text = ds.Tables(0).Rows(0).Item("QTYONHAND").ToString()
            Else
                MsgBox("Check Product Display Criteria", 16, "Inventory Manager")
                txtDisplay.Focus()
            End If

        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
    End Sub


End Class